from pypinyin import lazy_pinyin, Style
import psycopg2


def chinese_to_pinyin(chinese_str):
    """将中文转换为不带声调的拼音"""
    return ' '.join(lazy_pinyin(chinese_str))


def chinese_to_initials(chinese_str):
    """将中文转换为首字母"""
    return ''.join([i[0] for i in lazy_pinyin(chinese_str, style=Style.FIRST_LETTER)])


def stream_update_large_table(batch_size=1500):
    conn = psycopg2.connect(
        host="10.255.40.58",
        database="giscenter",
        user="postgres",
        password="eGova@pggis2023"
    )
    update_query = "UPDATE gis_poi_data_yace SET pinyin_abbr = %s WHERE id = %s"
    try:
        # 使用可滚动游标
        with conn.cursor() as cursor:
            cursor.execute("SELECT id, address FROM gis_poi_data_yace FOR UPDATE")
            processed = 0
            while True:
                rows = cursor.fetchmany(batch_size)
                if not rows:
                    break

                # 处理并更新当前批次
                update_values = []
                for row in rows:
                    id_val, col_val = row

                    new_value = chinese_to_pinyin(col_val).strip()  # 计算新值
                    update_values.append((new_value, id_val))
                with conn.cursor() as update_cursor:
                    update_cursor.executemany(update_query, update_values)
                    conn.commit()

                processed += len(rows)
                print(f"已处理 {processed} 行")

    except Exception as e:
        conn.rollback()
        print(f"更新失败: {e}")
    finally:
        conn.close()

if __name__ == '__main__':
    stream_update_large_table()
